1 Introduction

Hi, welcome to this third lesson, on charts and dashboard creation.

In the previous lesson, you explored your first dataset and created your first table chart.

In this lesson, you will keep on exploring the same dataset, create new types of charts and build your first dashboard.

2 Learning Objectives

By the end of this lesson,

3 Time Evolution - Line Chart

3.1 Create a line chart

Suppose we are interested in following the evolution of weekly hospitalisations in Switzerland over the last 2 years, and would like to create a new chart for that, using the switzerland.foph_hosp_d dataset we started exploring in the previous lesson.

Let’s go back to the Charts page, and click on the +CHART button in the upper right corner of the page to create a new chart.

The page named Create a new chart opens. You must do two things here:

  1. CHOOSE A DATASET, and
  2. CHOOSE A CHART TYPE.

In the field CHOOSE A DATASET, type or select from the drop-down list again the foph_hosp_d dataset.

In the CHOOSE CHART section, different sub-sections are available: Recommended tags, Category, and Tags.

In the Category sub-section, select the Evolution category. A number of corresponding charts will appear on the right panel. Let’s click on the first one and select the: Line Chart. It is the classic chart that visualised how metrics change over time.

We can then click on CREATE NEW CHART button in the bottom.

When you do so, a new Chart page opens, with pre-filled Dataset and Chart type fields.

You can see as well that for this type of chart, some parameters are mandatory. It is the case here for the METRICS field in the Query section. That is why it is colored in red, and annotated with an exclamation mark (!). Indeed, for line charts, METRICS field cannot be empty and one or many several metrics must be selected to be displayed.

3.2 Metrics and Aggregation

As at least one METRIC is mandatory, let’s click on + Add metric in the METRICS field. A pop-up window appears. When we select the SIMPLE menu, we are requested to fill the COLUMN to be displayed and how we would like to AGGREGATE it.

As we have daily new hospitalisations data in our dataset and we are interested visualising weekly hospitalisations, we will set COLUMN to entries, and AGGREGATE to SUM, since the number of hospitalisations (entries) per week is the SUM of the daily hospitalisation during that week :)

Based on your definition, this metric is now labeled SUM(entries). Unless you edit it, this is the label that will appear in the legend of your chart.

To edit the label of this metric (e.g. change it to “Number of entries”), click on the pencil icon, highlighted below:

When you are done, click SAVE in this pop-up window, then click RUN QUERYon the right panel of the Chart page. The result should be the following:

3.3 Time grain

By default the TIME GRAIN field in the Time section is set to Day. As we are interested by numbers of weekly hospitalisations, let’s change it to Week, for aggregating entries at the weekly-level, and RUN QUERYagain. You will get the result below, where you can directly see how it made the line smoother.

3.4 Group by Swiss Canton (State)

Currently all entries in our table (independently from georegion value) are summed by week. Let’s GROUP entries BY georegion, to see the evolution of the number of entries by georegion (Swiss canton).

In GROUP BY field, add georegion column, and press on RUN QUERY.

The results should look like the following:

Great! We can see now that data are separated by canton. But, we also see that we have data for the whole Switzerland (georegion = CH) and for Switzerland and Liechtenstein (georegion = CHFL) all together.

3.5 Filters

In order to focus on canton data only, we must FILTER out CH and CHFL data. In FILTERS, click on +Add filter, and set the filter to georegion NOT IN (CH, CHFL).

3.6 Finalise chart: Legend, Axes, Title

  • In case a legend was not added automatically, when you grouped by georegion, add it to see the correspondence between the line colors and the cantons. For that, go to the CUSTOMIZE tab (next to DATA tab), and tick the LEGEND box.

Note that the plot is interactive; you can show or hide lines from the chart by clicking or double-clicking on their associated legend item!

  • While we are in the CUSTOMIZE tab, let’s also add a name to the X and Y axes. In the X Axis section, write Time in the X AXIS LABEL field, and Number of hospitalisations in the Y AXIS LABEL field of the Y AXIS section.

And that’s the result, where we can see the different waves during the last 2 years, by Swiss canton.

  • Finally, as previously done with Table Chart, let’s:

    • Give a title to this line chart, for example Evolution of weekly COVID hospitalisations in Switzerland by Canton,
    • Add it to the dashboard you previously created (named COVID Hospitalisations in Geneva and Switzerland), and click on the SAVE & GO TO DASHBOARD.

The dashboard will open and you will see that your two charts (1) table chart and 2) line chart) were added, as below:

You can easily edit the format of the dashboard. To do so, click on the Edit button on the top right corner of the page.

It will open the Edit Dashboard page, below:

There, you can put the charts side by side (with drag and drop) and resize them to take the whole width of the page. When done, SAVE it, and get the following NICE result :)

4 Geographical distribution - Country Map

4.1 Create a country map chart

Now, let’s create a last chart. You already know how to create a new chart, right? :)

  1. Go to the Charts page, and
  2. Click on the +CHART button.

Suppose we are interested in creating a Map providing an overview of COVID hospitalisations across Switzerland’s cantons during the last month, so:

  1. In the field CHOOSE A DATASET, type again foph_hosp_d.
  2. In the CHOOSE CHART section, go to the Map Category, and select the Country Map in the right panel.
  3. Then, click on the CREATE NEW CHART button

4.2 Query

In the Query section of the Chart Page, fill :

  1. the COUNTRY field with Switzerland,
  2. the ISO 3166-2 CODES field with isocode, and
  3. the METRIC field with SUM(entries), as we did in the previous line chart.

click on the + symbol next to METRIC, select the SIMPLE panel, and set COLUMN to entries and AGGREGATE to SUM.

It should look like the following:

In this dataset, the isocode column allows us to link the canton data to the corresponding canton polygon on the country map. This isocode is a concatenation of the string 'CH-' with the value of georegion (‘CH’+georegion). For instance, canton Geneva, with georegion = 'GE', has and isocode = 'CH-GE'.

4.3 Filter

As we are interested here by canton data only, let’s again FILTER out data where georegion = ‘CH’ or georegion = ‘CHFL’. To do that, we will add a filter georegion NOT IN (‘CH’,’CHFL’), and RUN QUERY again, as follows:

4.4 Color scheme

If you are not fan of the color scheme, you can change it, by going to the Chart Options section (under the Query section), selecting for instance oranges in the LINEAR COLOR SCHEME field, and RUN QUERY again, to get the following result:

It does look quite nice :) What do you think?

4.5 Time range

As we did not edit the time-related elements yet, you have now on your map the total number of hospitalisation entries per canton, since February 2020, while we are interested by last month’s hospitalisations only.

To correct that, we should go to Time section in the middle panel, and set TIME RANGE to last month. For that, click on No filter value in the TIME RANGE field; this will open a pop-up window named Edit time range.

There, select:

  1. Last in RANGE TYPE,
  2. select last month in Configure Time Range Last…:, and
  3. click on the APPLY button in the bottom right of the window. The window will close and you will be able to RUN QUERY again.

4.6 Dynamic map

Below is the resulting map. Notice that the map is dynamic, when you pass your mouse over the cantons on the map, the name of the canton and the number of hospitalisation entries in this canton appear.

4.7 Finalise chart

It is now time to :

  1. Specify a title for the chart, for instance Number of COVID hospitalisations during last month,
  2. Save it, by clicking on +SAVE button in the middle panel,
  3. Add it to your dashboard, and
  4. Click on SAVE & GO TO DASHBOARD.

5 Dashboard

5.1 Finalise dashboard

When on the Dashboard page, Edit the format of the dashboard to:

  1. Move the map we just added under the first two charts, and
  2. Resize it to take the full width of the page.

If you have followed all the instructions above, you should get this result:

5.2 Publish the dashboard

To publish our dashboard and make it accessible to all on the EpiGraphHub platform, we need to do one last step:

  • Click on the Draft button on the right of the dashboard’s title

After this click, the dashboard shows up as Published!

Congratulations! You reached the end of this third lesson, where you have created two new types of charts, i.e. a line chart and a country map, and where you completed building your first dashboard.

Hopefully, you are now comfortable with displaying the evolution of quantitative values over time, and mapping geographical data. You should also know how quick it is to combine your different charts into a dashboard that can be easily shared with your colleagues.

While the dataset you have explored for now was made of data already aggregated at the geographical region level, in the next lesson you will explore a new dataset with raw individual data. See you there :)

Contributors

The following team members contributed to this lesson:

This work is licensed under the Creative Commons Attribution Share Alike license. Creative Commons License